package sealte.mynode.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import sealte.mynode.pojo.Essay;
import sealte.mynode.db.DBConnection;
import sealte.mynode.util.Tools;

/**
 * ESSAY Bean
 * @author SEALTE
 * 2012-11-14
 */
public class EssayBean {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	public boolean addNew(Essay essay){
		String sql = "INSERT INTO M_ESSAY(F_AUTHOR,F_WRITETIME,F_TITLE,F_CONTENT,F_MODIFYTIME) VALUES(?,?,?,?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, essay.getAuthorId());
			ps.setString(2, essay.getWriteTime());
			ps.setString(3, essay.getTitle());
			ps.setString(4, essay.getContent());
			ps.setString(5, essay.getModifyTime());
			ps.execute();
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			DBConnection.close(null, ps, conn);
		}
	}
	
	public List<Essay> listAll(int authorId){
		String sql = "SELECT E.F_ID,U.F_NAME,F_WRITETIME,F_TITLE,F_CONTENT,F_MODIFYTIME "
				   + "FROM M_ESSAY E,M_USER U WHERE E.F_AUTHOR=U.F_ID AND F_AUTHOR=? ORDER BY E.F_ID";
		List<Essay> all = null;
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, authorId);
			rs = ps.executeQuery();
			all = new ArrayList<Essay>();
			while(rs.next()){
				Essay essay = new Essay();
				essay.setAuthor(rs.getString("F_NAME"));
				essay.setAuthorId(authorId);
				essay.setContent(rs.getString("F_CONTENT").replaceAll("\n\r", "<br/>"));
				essay.setId(rs.getInt("F_ID"));
				essay.setModifyTime(Tools.getDateTimeRF(rs.getString("F_MODIFYTIME")));
				essay.setTitle(rs.getString("F_TITLE"));
				essay.setWriteTime(Tools.getDateTimeRF(rs.getString("F_WRITETIME")));
				all.add(essay);
			}
			return all;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			DBConnection.close(rs, ps, conn);
		}
	}
	
	public List<Essay> listAllExceptThis(int authorId){
		String sql = "SELECT E.F_ID,U.F_NAME,F_WRITETIME,F_TITLE,F_CONTENT,F_MODIFYTIME "
				   + "FROM M_ESSAY E,M_USER U WHERE E.F_AUTHOR=U.F_ID AND F_AUTHOR!=? ORDER BY E.F_ID";
		List<Essay> all = null;
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, authorId);
			rs = ps.executeQuery();
			all = new ArrayList<Essay>();
			while(rs.next()){
				Essay essay = new Essay();
				essay.setAuthor(rs.getString("F_NAME"));
				essay.setAuthorId(authorId);
				essay.setContent(rs.getString("F_CONTENT").replaceAll("\n\r", "<br/>"));
				essay.setId(rs.getInt("F_ID"));
				essay.setModifyTime(Tools.getDateTimeRF(rs.getString("F_MODIFYTIME")));
				essay.setTitle(rs.getString("F_TITLE"));
				essay.setWriteTime(Tools.getDateTimeRF(rs.getString("F_WRITETIME")));
				all.add(essay);
			}
			return all;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			DBConnection.close(rs, ps, conn);
		}
	}
	
	public Essay getInfoById(int id){
		String sql = "SELECT U.F_ID AUTHORID,U.F_NAME,F_WRITETIME,F_TITLE,F_CONTENT,F_MODIFYTIME "
				   + "FROM M_ESSAY E,M_USER U WHERE E.F_AUTHOR=U.F_ID AND E.F_ID=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				Essay essay = new Essay();
				essay.setId(id);
				essay.setAuthor(rs.getString("F_NAME"));
				essay.setAuthorId(rs.getInt("AUTHORID"));
				essay.setContent(rs.getString("F_CONTENT").replaceAll("\n\r", "<br/>"));
				essay.setModifyTime(Tools.getDateTimeRF(rs.getString("F_MODIFYTIME")));
				essay.setWriteTime(Tools.getDateTimeRF(rs.getString("F_WRITETIME")));
				essay.setTitle(rs.getString("F_TITLE"));
				return essay;
			}else{
				return null;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			DBConnection.close(rs, ps, conn);
		}
	}
}
